using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using SsisUtilities.PacMan.Components;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;

namespace SsisUtilities.PacMan.UI
{
    public partial class PackageManager : Form
    {
        private FileInfo packageFile;
        private FileInfo projectFile;
        private FileInfo solutionFile;
        private DirectoryInfo rootFolder;

        PackageCollectionUtil packages;

        PackageUtil referencePackage;
        
        public PackageManager()
        {
            InitializeComponent();
        }

        #region Select Package/Packages Control Event Handlers

        private void buttonSelectPackage_Click(object sender, EventArgs e)
        {
            fileDialog.Filter = "SSIS Packages|*.dtsx";
            fileDialog.Title = "Select the Integration Services Package to Open";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    packageFile = new FileInfo(fileDialog.FileName);
                    textPackagePath.Text = packageFile.FullName;
                    radioButtonPackage.Checked = true;
                    RefreshPackageCollection();
                }
                catch (Exception ex)
                {
                    ShowException(ex);
                }
            }
        }

        private void buttonSelectProject_Click(object sender, EventArgs e)
        {
            fileDialog.Filter = "Integration Services Projects|*.dtproj";
            fileDialog.Title = "Select the Integration Services Project to Open";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    projectFile = new FileInfo(fileDialog.FileName);
                    textProjectPath.Text = projectFile.FullName;
                    radioButtonProject.Checked = true;
                    RefreshPackageCollection();
                }
                catch (Exception ex)
                {
                    ShowException(ex);
                }
            }
        }

        private void buttonSelectSolution_Click(object sender, EventArgs e)
        {
            fileDialog.Filter = "Visual Studio Solutions|*.sln";
            fileDialog.Title = "Select the Visual Studio Solution to Open";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    solutionFile = new FileInfo(fileDialog.FileName);
                    textSolutionPath.Text = solutionFile.FullName;
                    radioButtonSolution.Checked = true;
                    RefreshPackageCollection();
                }
                catch (Exception ex)
                {
                    ShowException(ex);
                }
            }
        }

        private void buttonSelectRootFolder_Click(object sender, EventArgs e)
        {
            folderDialog.Description = "Select the root folder that contains SSIS packages to configure";
            if (folderDialog.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    rootFolder = new DirectoryInfo(folderDialog.SelectedPath);
                    textFolderPath.Text = rootFolder.FullName;
                    radioButtonFolder.Select();
                    RefreshPackageCollection();
                }
                catch (Exception ex)
                {
                    ShowException(ex);
                }
            }
        }

        #endregion
  
        #region Private Worker Routines

        /// <summary>
        /// Set the path to the custom xml config file based on the environment
        /// variable setting
        /// </summary>
        private void SetConfigFilePath(string environmentVariableName)
        {
            txtXmlConfigFilePath.Text = 
                Environment.GetEnvironmentVariable(environmentVariableName);
        }

        private void SaveAndRefreshPackages()
        {
            if (packages != null)
            {
                packages.Save();
                RefreshPackageCollection();
            }
        }

        private void BuildPackageCollection(PackageUtil package)
        {
            packages = new PackageCollectionUtil(package);
            SetAffectedPackagesMessage();
        }

        private void BuildPackageCollection(FileInfo projectFile)
        {
            packages = new PackageCollectionUtil(projectFile);
            SetAffectedPackagesMessage();
        }

        private void BuildPackageCollection(DirectoryInfo rootFolder)
        {
            packages = new PackageCollectionUtil(rootFolder);
            SetAffectedPackagesMessage();
        }

        private void RefreshPackageCollection()
        {
            if (radioButtonPackage.Checked)
            {
                BuildPackageCollection(new PackageUtil(packageFile));
            }
            else if (radioButtonProject.Checked)
            {
                BuildPackageCollection(projectFile);
            }
            else if (radioButtonSolution.Checked)
            {
                BuildPackageCollection(solutionFile);
            }
            else if (radioButtonFolder.Checked)
            {
                BuildPackageCollection(rootFolder);
            }
            BuildLookupLists();

            propertyGridBatchUpdate.SelectedObject = packages[0].SsisPackage;

            buttonValidate.Enabled = true;
            buttonSelectXmlConfigFile.Enabled = true;
            buttonApplyCustomXmlConfig.Enabled = true;
        }

        private void ShowException(Exception ex)
        {
            MessageBox.Show(ex.ToString(), "An error has occurred",
                MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

        private void SetAffectedPackagesMessage()
        {
            int packageCount = packages.Count;

            if (packageCount == 1)
            {
                affectedPackagesLabel.Text = 
                    "One (1) Package Will Be Affected By This Operation";
            }
            else
            {
                affectedPackagesLabel.Text = string.Format(
                    "{0} Packages Will Be Affected By This Operation", packageCount);
            }
            affectedPackagesLabel.Visible = true;
        }

        private bool ValidateVersionTextBox(TextBox versionTextBox)
        {
            if (!IsVersionValid(versionTextBox.Text))
            {
                validationErrorProvider.SetError(versionTextBox, 
                    "Please specify a positive integer for the major and minor version numbers.");
                return false;
            }
            else
            {
                validationErrorProvider.SetError(versionTextBox, "");
                return true;
            }
        }

        private bool IsVersionValid(string version)
        {
            int versionNumber = -1;
            bool isValueNumeric = false;

            try
            {
                versionNumber = int.Parse(version);
                isValueNumeric = true;
            }
            catch { /* We don't care - we're just testing for non-numeric values */ }

            if (!isValueNumeric && versionNumber >= 0)
            {
                return false;
            }
            else 
            {
                return true;
            }

        }

        private void BuildDataTypeList(ComboBox list)
        {
            list.DataSource = UiHelper.GetDataTypesList();
        }

        private bool ValidateVariableValueTextBox(TextBox valueTextBox, ComboBox dataTypeComboBox)
        {
            if (!SsisHelper.IsCastValid((TypeCode)dataTypeComboBox.SelectedValue,
                valueTextBox.Text))
            {
                validationErrorProvider.SetError(valueTextBox, 
                    string.Format("Please specify a value that is valid for the '{0}' data type.", 
                        dataTypeComboBox.SelectedValue));
                return false;
            }
            else
            {
                validationErrorProvider.SetError(valueTextBox, "");
                return true;
            }
        }

        private bool IsVariableNameValid(string variableName)
        {
            // From http://msdn2.microsoft.com/en-us/library/ms140285.aspx :
            // The naming of regular identifiers must follow these rules: 
            // The first character of the name must be a letter as defined by 
            // the Unicode Standard 2.0, or an underscore (_).
            // Subsequent characters can be letters or numbers as defined in 
            // the Unicode Standard 2.0, the underscore (_), @, $, and # characters. 

            if (variableName.Length < 1)
            {
                return false;
            }

            char firstChar = variableName.ToCharArray(0, 1)[0];

            bool isNameValid =
                (firstChar == '_') || (char.IsLetter(firstChar));

            if (isNameValid)
            {
                foreach (char letter in variableName.ToCharArray(1, variableName.Length -1))
                {
                    if (!(char.IsLetterOrDigit(letter)))
                    {
                        if (!(letter == '_' || letter == '@' || letter == '$' || letter == '#'))
                        {
                            isNameValid = false;
                            break;
                        }
                    }
                }
            }

            return isNameValid;    
        }

        private bool ValidateVariableNameTextBox(TextBox variableNameTextBox)
        {
            if (!IsVariableNameValid(variableNameTextBox.Text))
            {
                validationErrorProvider.SetError(variableNameTextBox,
                    "Please specify a valid variable name - see http://msdn2.microsoft.com/en-us/library/ms140285.aspx for the rules.");
                return false;
            }
            else
            {
                validationErrorProvider.SetError(variableNameTextBox, "");
                return true;
            }
        }

        private bool ValidatePackagesCollection()
        {
            if (packages == null)
            {
                MessageBox.Show("Please select one or more packages.", 
                    "No Packages Selected", MessageBoxButtons.OK);
                return false;
            }
            else
            {
                return true;
            }
        }

        private void BuildLookupLists()
        {
            BuildVariableList(comboVariables);
            BuildVariableList(comboVariablesForConnections);
            BuildVariableList(comboVariablesForValueList); 
            
            BuildConfigurationList(comboConfigurations);
            
            BuildConnectionManagerList(comboConnectionManagers);

            BuildConnectionManagerList(comboConnectionManagers4, true);
            BuildDataTypeList(comboDataTypes4);
        }

        private void BuildConnectionManagerList(ComboBox list)
        {
            if (packages != null)
            {
                list.DataSource = packages.GetConnectionManagers(checkOleDbOnly.Checked);
                list.DisplayMember = "Name";
            }
        }

        private void BuildConnectionManagerList(ComboBox list, bool oleDbOnly)
        {
            if (packages != null)
            {
                list.DataSource = packages.GetConnectionManagers(oleDbOnly);
                list.DisplayMember = "Name";
            }
        }

        private void BuildVariableList(ComboBox list)
        {
            if (packages != null)
            {
                foreach (KeyValuePair<string, Variable> v in packages.GetVariables())
                {
                    list.Items.Add(v.Value);
                }
                //list.DataSource = packages.GetVariables();
                list.DisplayMember = "Name";
            }
        }

        private void BuildConfigurationList(ComboBox list)
        {
            if (packages != null)
            {
                list.DataSource = packages.GetConfigurations();
                list.DisplayMember = "Name";
            }
        }

        private void UpdateVariableDetailControls(Variable var)
        {
            if (var != null)
            {
                textVarDetailName.Text = var.Name;
                textVarDetailNamespace.Text = var.Namespace;
                textVarDetailValue.Text = var.Value.ToString();
                textVarDetailExpression.Text = var.Expression;
                textVarDetailDescription.Text = var.Description;
                textVarDetailDataType.Text = var.DataType.ToString();
                checkVarDetailExpression.Checked = var.EvaluateAsExpression;
                checkVarDetailReadOnly.Checked = var.ReadOnly;

                PackageCollectionUtil.AffectedScope scope = packages.VariableExists(var);

                labelDeleteVariableAffectedMessage.Text = string.Format(
                    "{0} of the {1} packages selected will be affected by this operation.",
                    scope.ToString(), packages.Count);

                labelDeleteVariableAffectedMessage.Visible = true;

                if (packages.Count > 1)
                {
                    validationErrorProvider.SetError(comboVariables,
                        "IMPORTANT! Not all of the details displayed for this variable are guaranteed to be identical for all affected packages!");
                }
                else
                {
                    validationErrorProvider.SetError(comboVariables, "");
                }
            }
        }

        private void BuildValidationListView(ListView validationListView)
        {
            if (packages != null)
            {
                validationListView.Items.Clear();
                validationListView.Groups.Clear();

                string currentProjectName = "Unknown";

                foreach (PackageUtil package in packages)
                {
                    string packageLine = package.SsisPackage.Name + ": " +
                        package.ValidationStatus;
                    int packageImageIndex = -1;

                    if (package.ValidationStatus == "Success")
                    {
                        packageImageIndex = 2;
                    }
                    else if (package.ValidationStatus == "Failure")
                    {
                        packageImageIndex = 0;
                    }
                    else if (package.ValidationStatus == "Canceled")
                    {
                        packageImageIndex = 1;
                    }

                    if (currentProjectName != package.ProjectName)
                    {
                        validationListView.Groups.Add(package.ProjectName, package.ProjectName);
                        currentProjectName = package.ProjectName;
                    }

                    ListViewItem newItem = validationListView.Items.Add(packageLine, packageImageIndex);
                    newItem.Group = validationListView.Groups[package.ProjectName];

                }
            }
        }

        private void BuildValidationTreeView(TreeView validationTreeView)
        {
            if (packages != null)
            {
                validationTreeView.Nodes.Clear();

                string currentProjectName = "Unknown";

                foreach (PackageUtil package in packages)
                {
                    string packageLine = package.SsisPackage.Name + ": " +
                        package.ValidationStatus;
                    int packageImageIndex = -1;

                    if (package.ValidationStatus == "Success")
                    {
                        packageImageIndex = 2;
                    }
                    else if (package.ValidationStatus == "Failure")
                    {
                        packageImageIndex = 0;
                    }
                    else if (package.ValidationStatus == "Canceled")
                    {
                        packageImageIndex = 1;
                    }

                    if (currentProjectName != package.ProjectName)
                    {
                        validationTreeView.Nodes.Add(package.ProjectName, package.ProjectName, 6, 6);
                        currentProjectName = package.ProjectName;
                    }

                    // IMPORTANT: The validation tree view fails because of this next line
                    //            if you attempt to validate a folder tree and not a project
                    //            or solution!
                    TreeNode currentProjectNode = validationTreeView.Nodes[package.ProjectName];

                    currentProjectNode.Expand();

                    TreeNode currentPackageNode = currentProjectNode.Nodes.Add(
                        package.ProjectName + package.SsisPackage.Name, package.SsisPackage.Name,
                        packageImageIndex, packageImageIndex);

                    foreach (string errorMessage in package.ErrorMessages)
                    {
                        currentPackageNode.Nodes.Add(package.ProjectName + package.SsisPackage.Name + errorMessage,
                            errorMessage, 3, 3);
                    }

                    foreach (string warningMessage in package.WarningMessages)
                    {
                        currentPackageNode.Nodes.Add(package.ProjectName + package.SsisPackage.Name + warningMessage,
                            warningMessage, 4, 4);
                    }

                    foreach (string infoMessage in package.InfoMessages)
                    {
                        currentPackageNode.Nodes.Add(package.ProjectName + package.SsisPackage.Name + infoMessage,
                            infoMessage, 5, 5);
                    }

                }
            }
        }

        #endregion

        #region Set Version Tab Control Event Handlers

        private void textVersion_Validated(object sender, EventArgs e)
        {
            TextBox versionBox = (TextBox)sender;

            ValidateVersionTextBox(versionBox);
        }

        private void textVersion_Enter(object sender, EventArgs e)
        {
            TextBox versionBox = (TextBox)sender;
            versionBox.Select(0, versionBox.Text.Length);
        }

        private void buttonSetVersion_Click(object sender, EventArgs e)
        {
            if (!ValidatePackagesCollection())
            {
                return;
            }

            if (ValidateVersionTextBox(textMajorVersion) &&
                ValidateVersionTextBox(textMinorVersion))
            {
                int versionMajor = int.Parse(textMajorVersion.Text);
                int versionMinor = int.Parse(textMinorVersion.Text);
                string versionComment = textVersionComment.Text;
                bool resetVersionBuild = checkBoxResetBuild.Checked;

                packages.SetVersion(versionMajor, versionMinor, versionComment,
                    resetVersionBuild);
                SaveAndRefreshPackages();
            }
        }


        #endregion

        #region Add Variable Tab Control Event Handlers

        private void checkExpression_CheckedChanged(object sender, EventArgs e)
        {
            labelExpressionWarning.Visible = ((CheckBox)sender).Checked;
        }

        private void buttonAddVariable_Click(object sender, EventArgs e)
        {
            if (!ValidatePackagesCollection())
            {
                return;
            }

            bool createVariable = true;
            string fullVariablename = textNamespace.Text + "::" + textVariableName.Text;
            PackageCollectionUtil.AffectedScope affected = 
                packages.VariableExists(fullVariablename);
            string prompt = string.Format(
                    "A variable with the name {0} already exists in {1} of the {2} packages to be affected." +
                        Environment.NewLine + "These variables will be deleted and then re-created. Do you want to continue?",
                        fullVariablename, affected, packages.Count);

            if (affected != PackageCollectionUtil.AffectedScope.None)
            {
                if (MessageBox.Show(prompt, "Are You Certain?", MessageBoxButtons.YesNoCancel) 
                    != DialogResult.Yes)
                {
                    createVariable = false;
                }
            }

            if (createVariable)
            {
                packages.CreateVariable(textVariableName.Text, 
                    (TypeCode)comboDataTypes.SelectedValue,
                    checkReadOnly.Checked, textNamespace.Text, textValue.Text,
                    checkExpression.Checked, textExpression.Text,
                    textDescription.Text);
                SaveAndRefreshPackages();
            }
            
            // Reset the controls for the next variable
            //textVariableName.Text = "";
            //textValue.Text = "";
            //textDescription.Text = "";
            //textExpression.Text = "";
            //checkExpression.Checked = false;
            //checkReadOnly.Checked = false;
        }

        private void textVariableName_Validated(object sender, EventArgs e)
        {
            ValidateVariableNameTextBox((TextBox)sender);
        }

        private void dataType_Validated(object sender, EventArgs e)
        {
            ValidateVariableValueTextBox(textValue, comboDataTypes);
        }
        #endregion

        #region Form Event Handlers

        private void PackageManager_Load(object sender, EventArgs e)
        {
            BuildDataTypeList(comboDataTypes);
            SetConfigFilePath("CIA_Config_File_Path");

#if QA_BUILD

            // We don't want the QA folks actually modifying any packages,
            // so we're hiding all but the validation tab to make it harder
            // for them to use this particular rope to hang themselves

            foreach (TabPage page in tabControl1.TabPages)
            {
                if (page != tabValidate)
                {
                    tabControl1.TabPages.Remove(page);
                }
            }

#endif
        }

        #endregion

        #region Delete Variable Tab Control Event Handlers

        private void comboVariables_SelectedValueChanged(object sender, EventArgs e)
        {
            Variable v = (Variable)((ComboBox)sender).SelectedItem;
            UpdateVariableDetailControls(v);
        }

        private void buttonDeleteVariable_Click(object sender, EventArgs e)
        {
            if (packages != null)
            {
                packages.DeleteVariable((Variable)comboVariables.SelectedItem);
                SaveAndRefreshPackages();
            }
        }

        #endregion

        #region Delete Configuration Tab Control Event Handlers

        private void comboConfigurations_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                Configuration currentConfig = ((Configuration)((ComboBox)sender).SelectedItem);
                textConfigString.Text = currentConfig.ConfigurationString;
                textConfigDescription.Text = currentConfig.Description;
                textConfigPackagePath.Text = currentConfig.PackagePath;
                textConfigType.Text = currentConfig.ConfigurationType.ToString();

                if (packages.Count > 1)
                {
                    validationErrorProvider.SetError(comboConfigurations,
                        "IMPORTANT! Not all of the details displayed for this configuration are guaranteed to be identical for all affected packages!");
                }
                else
                {
                    validationErrorProvider.SetError(comboVariables, "");
                }
            }
            catch
            {
                // TODO: Determine an appropriate response (if any) here
            }
        }

        private void buttonDeleteConfiguration_Click(object sender, EventArgs e)
        {
            packages.DeleteConfiguration(
                ((Configuration)((ComboBox)comboConfigurations).SelectedItem).Name);
            SaveAndRefreshPackages();
        }

        #endregion

        #region Connection Manager Tab Control Event Procedures

        private void checkOleDbOnly_CheckedChanged(object sender, EventArgs e)
        {
            BuildConnectionManagerList(comboConnectionManagers);
        }

        private void buttonSetConnectionExpression_Click(object sender, EventArgs e)
        {
            if (packages != null)
            {
                packages.UpdateConnectionManagerExpression(
                    ((ConnectionManager)comboConnectionManagers.SelectedItem).Name,
                    "ConnectionString",
                    (SsisHelper.GetVariableExpression((Variable)comboVariablesForConnections.SelectedItem)));
                SaveAndRefreshPackages();
            }
        }

        private void buttonRemoveDataSource_Click(object sender, EventArgs e)
        {
            if (solutionFile != null)
            {
                foreach (FileInfo dtprojFile in SlnHelper.GetProjects(solutionFile))
                {
                    DtprojHelper.DeleteDataSources(dtprojFile);
                }
            }
            else if (projectFile != null)
            {
                DtprojHelper.DeleteDataSources(projectFile);
            }
            else
            {
                MessageBox.Show("This option is only available when working with a project \n or a solution, as the project file must be updated.",
                    "Please Select A Project or Solution File", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
        }

        #endregion

        #region Complex Example Tab Control Event Procedures

        private void buttonBatch4_Click(object sender, EventArgs e)
        {
            foreach (PackageUtil package in packages)
            {
                Package ssisPackage = package.SsisPackage;
                string connectionManagerName = ((ConnectionManager)comboConnectionManagers4.SelectedItem).Name;

                if (ssisPackage.Connections.Contains(connectionManagerName))
                {
                    package.CreateVariable(textNewVariableName4.Text,
                        (TypeCode)comboDataTypes4.SelectedItem,
                        false,
                        textNewVariableNamespace4.Text,
                        textNewVariableValue4.Text,
                        false,
                        "",
                        textNewVariableDescription4.Text);

                    package.UpdateConnectionManagerExpression(
                        connectionManagerName,
                        "ConnectionString",
                        (SsisHelper.GetVariableExpression(textNewVariableName4.Text,
                            textNewVariableNamespace4.Text)));
                }
            }
            SaveAndRefreshPackages();
        }

        private void comboConnectionManagers4_SelectedIndexChanged(object sender, EventArgs e)
        {
            int totalPackageCount = 0;
            int matchingPackageCount = 0;

            foreach (PackageUtil package in packages)
            {
                totalPackageCount++;
                Package ssisPackage = package.SsisPackage;
                string connectionManagerName = ((ConnectionManager)((ComboBox)sender).SelectedItem).Name;

                if (ssisPackage.Connections.Contains(connectionManagerName))
                {
                    matchingPackageCount++;
                }
            }
            labelMatchingPackageCount4.Text = string.Format(
                "{0} of {1} Total Packages Match this Filter and will be Affected by this Operation",
                matchingPackageCount,
                totalPackageCount);
            labelMatchingPackageCount4.Visible = true;
        }

        #endregion

        #region Validation Tab Control Event Procedures

        private void buttonValidate_Click(object sender, EventArgs e)
        {
            labelValidationWait.Visible = true;

            // First, build the validation batch script
            if (packages != null)
            {
                textBoxValidationScripts.Text = "";

                foreach (string script in packages.GetValidateScript())
                {
                    textBoxValidationScripts.Text += script;
                    textBoxValidationScripts.Text += Environment.NewLine;
                }
            }

            // Next, perform an actual validation
            packages.Validate();
            //BuildValidationListView(listviewValidationResults);
            BuildValidationTreeView(treeViewValidationResults);
            labelValidationWait.Visible = false;
        }

        #endregion

        private void button1_Click(object sender, EventArgs e)
        {
            List<string> taskTypes = packages.GetDiscreteExecutableTypes();

            foreach (string type in taskTypes)
            {
                comboTaskTypes.Items.Add(type);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //PackageUtil pack = packages[0];

            //System.Diagnostics.Debug.WriteLine("Package Name: " + pack.SsisPackage.Name);

            //EnumerateExecutables(pack.SsisPackage.Executables);


            //foreach (DtsEventHandler hand in pack.SsisPackage.EventHandlers)
            //{
            //    System.Diagnostics.Debug.WriteLine(hand.Name);
            //    EnumerateExecutables(hand.Executables);
            //}

            ////System.Diagnostics.Debug.WriteLine();

            ////DtsEventHandler onPreExecute = pack.SsisPackage.EventHandlers["OnPreExecute"];
            //TaskHost scriptTaskHost = GetScriptTask(pack.SsisPackage);

            //EnumerateProperties(scriptTaskHost);

            //ScriptTask script = scriptTaskHost.InnerObject as ScriptTask;

            //ScriptTaskCodeProvider foo = script.CodeProvider;

            //string moniker = string.Format("dts://Scripts/{0}/{0}.vsaproj", script.VsaProjectName);

            //string code = script.CodeProvider.GetSourceCode(moniker);

            //System.Diagnostics.Debug.WriteLine(code);

            //System.Diagnostics.Debug.WriteLine("Do something interesting here in the Command window please.");
        }

        private void EnumerateProperties(TaskHost task)
        {
            foreach (DtsProperty prop in task.Properties)
            {
                try
                {
                    System.Diagnostics.Debug.WriteLine(prop.Name + ": " + prop.GetValue(task).ToString());
                }
                catch
                {
                    System.Diagnostics.Debug.WriteLine(prop.Name + ": {UNKNOWN}");
                }
            }
        }

        private void EnumerateExecutables(Executables execs)
        {
            foreach (Executable exec in execs)
            {
                string exeTypeName = string.Empty;

                if (exec is TaskHost)
                {
                    exeTypeName = (exec as TaskHost).InnerObject.ToString();
                }
                else if (exec is Sequence)
                {
                    exeTypeName = "Sequence Container";
                }
                else if (exec is ForEachLoop)
                {
                    exeTypeName = "Foreach Loop Container";
                }
                else if (exec is ForLoop)
                {
                    exeTypeName = "For Loop Container";
                }

                System.Diagnostics.Debug.WriteLine(exeTypeName);
            }
        }

        private TaskHost GetScriptTask(Package pack)
        {
            try
            {
                DtsEventHandler hand = pack.EventHandlers["OnPreExecute"];
                return hand.Executables[0] as TaskHost;
            }
            catch
            {
                return null;
            }
        }

        private void buttonSelectReferencePackage_Click(object sender, EventArgs e)
        {
            fileDialog.Filter = "SSIS Packages|*.dtsx";
            fileDialog.Title = "Select the Integration Services Package to Open";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    FileInfo referencePackageFile = new FileInfo(fileDialog.FileName);
                    textReferencePackagePath.Text = referencePackageFile.FullName;
                    referencePackage = new PackageUtil(referencePackageFile);
                }
                catch (Exception ex)
                {
                    ShowException(ex);
                }
            }
        }

        private void buttonCopyScriptTask_Click(object sender, EventArgs e)
        {
            packages.CopyScriptTaskPrototype(referencePackage);
            packages.Save();
        }

        private void buttonSelectXmlConfigFile_Click(object sender, EventArgs e)
        {
            fileDialog.Filter = "XML Files|*.xml";
            fileDialog.Title = "Select the Custom XML Configuration File to Open";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    FileInfo configFile = new FileInfo(fileDialog.FileName);
                    txtXmlConfigFilePath.Text = configFile.FullName;

                    XmlConfigHelper configHelper = new XmlConfigHelper(configFile.FullName);

                    List<string> configSubsets = configHelper.ConfigurationSubsetNames;
                    comboConfigurationSubsets.DataSource = configSubsets;
                }
                catch (Exception ex)
                {
                    ShowException(ex);
                }
            }
        }

        private void comboConfigurationSubsets_SelectedValueChanged(object sender, EventArgs e)
        {
            string currentConfigSubsetName = (string)((ComboBox)sender).SelectedValue;

            Dictionary<string, string> variablesToConfigure =
                packages.GetConfiguredVariableValues(currentConfigSubsetName, 
                    txtXmlConfigFilePath.Text);

            txtVariableValues.Text = string.Empty;

            StringBuilder variableListBuilder = new StringBuilder(500);

            foreach (KeyValuePair<string, string> var in variablesToConfigure)
            {
                variableListBuilder.Append(var.Key + ": " + var.Value + Environment.NewLine);
            }

            txtVariableValues.Text = variableListBuilder.ToString();


        }

        private void buttonApplyCustomXmlConfig_Click(object sender, EventArgs e)
        {
            packages.ApplyConfigurationSubset(txtXmlConfigFilePath.Text, 
                comboConfigurationSubsets.SelectedValue.ToString());

            packages.Save();
        }

        private void buttonDevConnManagers_Click(object sender, EventArgs e)
        {
            foreach (PackageUtil package in packages)
            {
                foreach (ConnectionManager cm in package.SsisPackage.Connections)
                {
                    System.Diagnostics.Debug.WriteLine(
                        string.Format("Connection '{0}' has type {1} and properties:",
                            cm.Name, cm.GetType()));
                    foreach (DtsProperty p in cm.Properties)
                    {
                        string propValue = "UNKNOWN";

                        try
                        {
                            propValue = p.GetValue(cm).ToString();
                        }
                        catch { }

                        System.Diagnostics.Debug.WriteLine(
                            string.Format("    {0} = '{1}'",
                            p.Name, propValue));
                    }
                    System.Diagnostics.Debug.WriteLine("");
                }
            }
        }

        private void buttonDocConnectionManagers_Click(object sender, EventArgs e)
        {
            textBoxDocs.Text += "CONNECTION MANAGERS" + Environment.NewLine + Environment.NewLine;

            SortedDictionary<string, PackageItem> distinctConnectons = new SortedDictionary<string, PackageItem>();

            foreach (PackageUtil pu in packages)
            {
                foreach (ConnectionManager cm in pu.SsisPackage.Connections)
                {
                    if (!distinctConnectons.ContainsKey(cm.Name))
                    {
                        distinctConnectons.Add(cm.Name,
                            new PackageItem(cm.Name, pu.SsisPackage.Name,
                                cm.ConnectionString));
                    }
                    else
                    {
                        distinctConnectons[cm.Name].Packages.Add(pu.SsisPackage.Name,
                            cm.ConnectionString);
                    }
                }
            }

            foreach (KeyValuePair<string, PackageItem> cmi in distinctConnectons)
            {
                textBoxDocs.Text += string.Format("{0} - Found in {1} {2} {3}",
                    cmi.Key, cmi.Value.Packages.Count, 
                    cmi.Value.Packages.Count == 1 ? "Package" : "Packages",
                    Environment.NewLine);

                foreach (KeyValuePair<string, string> cp in cmi.Value.Packages)
                {
                    textBoxDocs.Text += string.Format("    {0} - {1} {2}",
                    cp.Key, 
                    cp.Value,
                    Environment.NewLine);
                }
            }
            textBoxDocs.Text += Environment.NewLine;
        }

        private void buttonClearDocs_Click(object sender, EventArgs e)
        {
            textBoxDocs.Text = "";
        }

        private void buttonDocConfigurations_Click(object sender, EventArgs e)
        {
            textBoxDocs.Text += "CONFIGURATIONS" + Environment.NewLine + Environment.NewLine;

            SortedDictionary<string, PackageItem> distinctConfigurations = new SortedDictionary<string, PackageItem>();

            foreach (PackageUtil pu in packages)
            {
                foreach (Configuration c in pu.SsisPackage.Configurations)
                {
                    if (!distinctConfigurations.ContainsKey(c.Name + "(" + c.ConfigurationType + ")"))
                    {
                        distinctConfigurations.Add(c.Name + "(" + c.ConfigurationType + ")",
                            new PackageItem(c.Name + "(" + c.ConfigurationType + ")", pu.SsisPackage.Name,
                                c.ConfigurationString));
                    }
                    else
                    {
                        distinctConfigurations[c.Name + "(" + c.ConfigurationType + ")"].Packages.Add(pu.SsisPackage.Name,
                            c.ConfigurationString);
                    }
                }
            }

            foreach (KeyValuePair<string, PackageItem> ci in distinctConfigurations)
            {
                textBoxDocs.Text += string.Format("{0} - Found in {1} {2} {3}",
                    ci.Key, ci.Value.Packages.Count,
                    ci.Value.Packages.Count == 1 ? "Package" : "Packages",
                    Environment.NewLine);

                foreach (KeyValuePair<string, string> cp in ci.Value.Packages)
                {
                    textBoxDocs.Text += string.Format("    {0} - {1} {2}",
                    cp.Key,
                    cp.Value,
                    Environment.NewLine);
                }
            }
            textBoxDocs.Text += Environment.NewLine;

        }

        class PackageItem
        {
            public string Name;
            public SortedList<string, string> Packages;

            public PackageItem(string name, string packageName, string value)
            {
                Name = name;
                Packages = new SortedList<string, string>();
                Packages.Add(packageName, value);
            }
        }

        private void buttonCustomValidate_Click(object sender, EventArgs e)
        {
            // First, perform the custom validation
            packages.CustomValidate();

            // Then display the results
            BuildValidationTreeView(treeViewCustomValidationResults);
            labelValidationWait.Visible = false;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            string oldName = "LocalHost.AdventureWorksDW";
            string newName = "AWDW";

            foreach (PackageUtil p in packages)
            {
                if (p.SsisPackage.Connections.Contains(oldName))
                {
                    p.SsisPackage.Connections[oldName].Name = newName;
                }
            }
            packages.Save();
        }

        private void buttonExampleAddConfiguration_Click(object sender, EventArgs e)
        {
            AddConnectionManagerConfiguration("Staging DB", "DW_Config_Path");
        }

        private void AddConnectionManagerConfiguration(string connectionManagerName,
            string environmentVariableName)
        {
            if (packages != null)
            {
                // The root folder for the packages collection is a "reasonable
                // default" for storing the XML config file
                string configFilePath = Path.Combine(packages.RootCollectionPath,
                    connectionManagerName + ".dtsConfig");

                // Create an environment variable that references the XML config file

                Environment.SetEnvironmentVariable(environmentVariableName,
                    configFilePath, EnvironmentVariableTarget.Machine);

                foreach (PackageUtil pu in packages)
                {
                    Package p = pu.SsisPackage;
                    bool isFirstPackage = true;

                    // Update each package in the packages collection
                    if (p.Connections.Contains(connectionManagerName))
                    {
                        if (isFirstPackage)
                        {
                            // Save the configuration to an XML file
                            SaveXmlConfigFile(configFilePath, p, connectionManagerName);
                            isFirstPackage = false;
                        }

                        // Ensure that configurations are enabled
                        p.EnableConfigurations = true;

                        string configurationName = connectionManagerName + "_Configuration";

                        // Remove any existing configuration with the same name
                        if (p.Configurations.Contains(configurationName))
                        {
                            p.Configurations.Remove(configurationName);
                        }

                        // Create a new indirect XML configuration
                        Configuration c = p.Configurations.Add();
                        c.Name = configurationName;
                        c.ConfigurationType = DTSConfigurationType.IConfigFile;
                        c.ConfigurationString = environmentVariableName;
                    }
                }
                packages.Save();
            }
        }

        private void SaveXmlConfigFile(string configFilePath, 
            Package sourcePackage, string connectionManagerName)
        {
            XmlTextWriter xw = new XmlTextWriter(configFilePath, Encoding.UTF8);

            xw.Formatting = Formatting.Indented;
            xw.WriteStartDocument();

            xw.WriteStartElement("DTSConfiguration");
            xw.WriteStartElement("DTSConfigurationHeading");

            xw.WriteStartElement("DTSConfigurationFileInfo");
            xw.WriteAttributeString("GeneratedBy", sourcePackage.CreatorName);
            xw.WriteAttributeString("GeneratedFromPackageName", sourcePackage.Name);
            xw.WriteAttributeString("GeneratedFromPackageID", sourcePackage.ID);
            xw.WriteAttributeString("GeneratedDate", DateTime.Now.ToString("G"));
            xw.WriteAttributeString("LastModifiedDate", DateTime.Now.ToShortDateString());
            xw.WriteAttributeString("Description", sourcePackage.Description);
            xw.WriteEndElement(); // Close the DTSConfigurationFileInfo element 
            xw.WriteEndElement(); // Close the DTSConfigurationHeading element 

            xw.WriteStartElement("Configuration");
            xw.WriteAttributeString("ConfiguredType", "Property");
            xw.WriteAttributeString("Path", string.Format(
                "\\Package.Connections[{0}].Properties[ConnectionString]",
                connectionManagerName));
            xw.WriteAttributeString("ValueType", "String");

            xw.WriteElementString("ConfiguredValue",
                sourcePackage.Connections[connectionManagerName].ConnectionString);

            xw.WriteEndElement(); // Close the Configuration element 
            xw.WriteEndElement(); // Close the DTSConfiguration element 

            xw.Flush();
            xw.Close();
        }

        private void comboVariablesForValueList_SelectedValueChanged(object sender, EventArgs e)
        {
            string variableName = ((Variable)((ComboBox)sender).SelectedItem).Name;

            RefreshVariableValueList(variableName, textBoxVariableValueFilter.Text);
        }



        private void RefreshVariableValueList(string variableName, string variableValueFilter)
        {
            listViewVariableValues.Items.Clear();

            SortedDictionary<string, Variable> variableValues = packages.GetVariableValues(variableName);

            foreach (KeyValuePair<string, Variable> packageVariable in variableValues)
            {
                if (packageVariable.Value.Value.ToString().Contains(variableValueFilter))
                {
                    ListViewItem varItem = new ListViewItem(packageVariable.Key);
                    varItem.SubItems.Add(packageVariable.Value.Value.ToString());
                    listViewVariableValues.Items.Add(varItem);
                }
            }
        }

        private void buttonRefreshValueVariableList_Click(object sender, EventArgs e)
        {
            RefreshVariableValueList(((Variable)(comboVariablesForValueList.SelectedValue)).Name,
                textBoxVariableValueFilter.Text);
        }

        private void buttonEnumerateIDs_Click(object sender, EventArgs e)
        {
            if (packages != null)
            {
                SortedDictionary<string, List<string>> ids = packages.GetPackageIDs();
                BuildPackageIdTreeView(ids);
            }
        }

        private void BuildPackageIdTreeView(SortedDictionary<string, List<string>> packageIDs)
        {
            treeViewPackageIDs.Nodes.Clear();
            foreach (KeyValuePair<string, List<string>> id in packageIDs)
            {
                if (!checkBoxShowOnlyDuplicates.Checked || id.Value.Count > 1)
                {
                    // add a node for the ID
                    TreeNode newNode = treeViewPackageIDs.Nodes.Add(id.Key);
                    // add a child node for each package
                    foreach (string packagePath in id.Value)
                    {
                        newNode.Nodes.Add(packagePath);
                    }

                    newNode.Expand();
                }
            }
        }

    }

    
}
